
libname hannah '/projects/users/########/Snapshot2022/IntermediateData';
libname hantmp '/projects/users/########/Snapshot2022/IntermediateData/TempData';
libname ans '/projects/users/########/Snapshot2022/AnalysisData';

/*
[] brackets indicate internal census variable name that can't be disclosed.

only at least two quarters that year
only all quarters that year
only at least half of the quarters in sample (28 quarters)
only positive employment

All with and without worker-spell FE

log earnings
log average quarterly earnings: log([annual earnings]/4*unemp_instate)
CHANGE in earnings
Change in earnings relative to county-level last year's employment, with change in capacity relative to last year's employment. 
OR because we want per-capita and earnings already IS, just normalise right hand side!
(and above with one year lags as controls, IV also relative to last year's employment). 

employ
employ_in

controls: frst_enter new_spell end_spell run mover

more outcomes:
frst_enter, new_spell, end_spell, run - controlling for mover
new_spell conditional on frst_enter

outcome: mover, and controlling for run end_spell, new_spell, frst_enter
*/

/*
Need to create county-year in-state employment panel. 
*/

/*
* county-year in-state employment panel;
proc sql;
  create table ans.county_emp as
  select [county id], year, 
  sum(unemp_instate) as cnty_unemp_instate, 
  sum(min_unemp_instate) as cnty_min_unemp_instate, 
  sum(unemp_all) as cnty_unemp_all, 
  count(unemp_instate) as cnty_pop
  from ans.ehf_icf_analysis
  group by [county id], year
  order by [county id], year;
quit;
*/

* male piks;
proc sql;
  create table hantmp.male_piks as
  select * from hannah.pikspine where female = 0
  order by pik;
quit;
proc sort data=hantmp.male_piks nodupkey;
  by pik;
run;

* female piks;
proc sql;
  create table hantmp.female_piks as
  select * from hannah.pikspine where female = 1
  order by pik;
quit;
proc sort data=hantmp.female_piks nodupkey;
  by pik;
run;


* white piks;
proc sql;
  create table hantmp.white_piks as
  select * from hannah.pikspine where white = 1
  order by pik;
quit;
proc sort data=hantmp.white_piks nodupkey;
  by pik;
run;

* black piks;
proc sql;
  create table hantmp.black_piks as
  select * from hannah.pikspine where black = 1
  order by pik;
quit;
proc sort data=hantmp.black_piks nodupkey;
  by pik;
run;

* hispanic piks;
proc sql;
  create table hantmp.hispanic_piks as
  select * from hannah.pikspine where hispanic = 1
  order by pik;
quit;
proc sort data=hantmp.hispanic_piks nodupkey;
  by pik;
run;

* asian piks;
proc sql;
  create table hantmp.asian_piks as
  select * from hannah.pikspine where asian = 1
  order by pik;
quit;
proc sort data=hantmp.asian_piks nodupkey;
  by pik;
run;

* indnat piks;
proc sql;
  create table hantmp.indnat_piks as
  select * from hannah.pikspine where ind_nat = 1
  order by pik;
quit;
proc sort data=hantmp.indnat_piks nodupkey;
  by pik;
run;


* nohigh piks;
proc sql;
  create table hantmp.nohigh_piks as
  select * from hannah.pikspine where nohigh = 1
  order by pik;
quit;
proc sort data=hantmp.nohigh_piks nodupkey;
  by pik;
run;

* highsch piks;
proc sql;
  create table hantmp.highsch_piks as
  select * from hannah.pikspine where highsch = 1
  order by pik;
quit;
proc sort data=hantmp.highsch_piks nodupkey;
  by pik;
run;

* somecoll piks;
proc sql;
  create table hantmp.somecoll_piks as
  select * from hannah.pikspine where somecoll = 1
  order by pik;
quit;
proc sort data=hantmp.somecoll_piks nodupkey;
  by pik;
run;

* college piks;
proc sql;
  create table hantmp.college_piks as
  select * from hannah.pikspine where college = 1
  order by pik;
quit;
proc sort data=hantmp.college_piks nodupkey;
  by pik;
run;

